package com.yc.fruitmarket.biz.impl;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.yc.fruitmarket.biz.orderBiz;
import com.yc.fruitmarket.dao.DBHelper;

public class orderBizImpl implements orderBiz {
	private DBHelper db = new DBHelper();
	
//待付款状态
	@Override
	public int waitPay(String uid, String cids, String countMenoys, String num, String aid, String did) {
		String sql = "";
		int reslut1 = 0;
		if("".equals(did)) {
			 sql = "insert into `order` values(null,?,now(),?,?,?,null,0,null,null)";
			 reslut1 = db.doUpdate(sql, uid,countMenoys,num,aid);
		}else {
			String didnum = "select disnum from discountcard where did = ?";
			int dnum = Integer.parseInt(db.find(didnum, did).get(0).get("disnum"));
			if(dnum - 1 > 0) {
				String updatedid = "update discountcard set disnum = disnum - 1 where did = ?";
				db.doUpdate(updatedid, did);
				sql = "insert into `order` values(null,?,now(),?,?,?,?,0,null,null)";
				reslut1 = db.doUpdate(sql, uid,countMenoys,num,aid,did);
			}else {
				String updatedid = "update discountcard set status = 0,disnum = 0 where did = ?";
				db.doUpdate(updatedid, did);
				sql = "insert into `order` values(null,?,now(),?,?,?,?,0,null,null)";
				reslut1 = db.doUpdate(sql, uid,countMenoys,num,aid,did);
			}
		} 
		if (reslut1 <= 0) {  
	        // 处理订单插入失败的情况  
	        return -1;  
	    }  
		String maxoid = "select max(oid) from `order` where uid = ?";
		maxoid = db.find(maxoid,uid).get(0).get("max(oid)");
		if (maxoid == "" || maxoid == null) {  
	        // 处理找不到最大ID的情况  
	        return -2;  
	    }  
		String[] cid = cids.split(",");
		String forfindsql = "";
		String pid = "";
		String cartnum = "";
		String outprice = "";
		String forupdatesql = "";
		for(String s : cid) {
			s = s.trim();
			forfindsql = "select cart.pid as pid, cartnum, outprice from product,cart where product.pid = cart.pid and cart.cid = ?";
			List<Map<String, String>> list = db.find(forfindsql, s);
			pid = list.get(0).get("pid");
			cartnum = list.get(0).get("cartnum");
			outprice = list.get(0).get("outprice");
			//////////////////////////状态设的0
			forupdatesql = "insert into order_detail values(null,?,?,?,?,0)";
			int reslut2 = db.doUpdate(forupdatesql, pid,maxoid,cartnum,outprice);
			if (reslut2 <= 0) {  
                // 处理订单详情插入失败的情况  
                // 这里可能需要回滚之前的订单插入，取决于你的事务管理策略  
                return -3;  
            }  
		}
		return Integer.parseInt(maxoid);
	}
//支付成功修改状态
	@Override
	public int pay(String oid,String uid,String countmoneys) {
		
		String sql3 = "update `order` set status = 1 where oid = ?";
		System.out.println("结果"+db.doUpdate(sql3, oid));
		////////////////////////////
		String sql = "update user set balance = balance - ? where uid = ?";
		db.doUpdate(sql, countmoneys,uid);
		String sql2 = "update user set balance = balance + ? where status = 1";
		db.doUpdate(sql2, countmoneys);
		return db.doUpdate(sql3, oid);
	}
	
	
	
	//获取订单信息
	@Override
	public List<Map<String, String>> getordersL(String status) {
		String sql = "";
		if("-1".equals(status)) {
			sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid";
		}else {
			sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid and a.status = " + status;
		}
		List<Map<String, String>> list = db.find(sql);
		for(int i = 0; i < list.size(); i++) {
			 if("".equals(list.get(i).get("did")) || list.get(i).get("did") == null) {
				 list.get(i).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list.get(i).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list.get(i).put("discount", list2.get(0).get("discount"));
			 }
		 }
		return list;
	}
	
	//条件查询
	@Override
	public List<Map<String, String>> byselect(String status, String oid, String uname, String pname) {
		String sql = "";
		if("3,5".equals(status)) {
			sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid and a.status in (3,4,5)";
		}else {
			sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid and a.status = " + status;
		}
		System.out.println(sql);
		if(!"".equals(oid) && oid != null) {
			sql += " and oid = " + oid;
		}
		if(!"".equals(uname) && uname != null) {
			sql += " and uname like '%"+uname+"%'";
		}
		if(!"".equals(pname) && pname != null) {
			sql += " and `order`.oid in (select oid from order_detail,product where order_detail.pid = product.pid and pname like '%"+pname+"%' )";
		}
		List<Map<String, String>> list = db.find(sql);
		for(int i = 0; i < list.size(); i++) {
			 if("".equals(list.get(i).get("did")) || list.get(i).get("did") == null) {
				 list.get(i).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list.get(i).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list.get(i).put("discount", list2.get(0).get("discount"));
			 }
		 }
		return list;
	}
	
	//分页查询
	@Override
	public List<Map<String, String>> pageL(String oid, String uname, String pname, String status, int pagenum, String start, String end) {
		String sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid";
		if(!"".equals(oid) && oid != null) {
			sql += " and oid = " + oid;
		}
		if(!"".equals(uname) && uname != null) {
			sql += " and uname like '%"+uname+"%'";
		}
		if(!"".equals(pname) && pname != null) {
			sql += " and a.oid in (select oid from order_detail,product where order_detail.pid = product.pid and pname like '%"+pname+"%' )";
		}
		if(!"".equals(status) && status != null) {
			if(!"-1".equals(status)) {
				sql += " and a.status = " + status;
			}
		}
		if(!"".equals(start)) {
			start = start.substring(0, start.indexOf(" ")) + " 00:00:00";
			end = end.substring(0, end.indexOf(" ")) + " 00:00:00";
			//一天
			if(start.equals(end)) {
				start = start.substring(0, start.indexOf(" "));
				sql += " and time like '"+start+"%'";
			}else {
				//多天
				sql += " and time  between '"+start+"' and '"+end+"'";
			}
		}
		List<Map<String, String>> list = db.find(sql);
		String a = String.valueOf(list.size());
		 sql += " limit ?,10";
		 List<Map<String, String>> list1 = db.find(sql, pagenum * 2);
		 for(Map<String, String> map : list1) {
				map.put("count", a);
			}
		 for(int i = 0; i < list1.size(); i++) {
			 if("".equals(list1.get(i).get("did")) || list1.get(i).get("did") == null) {
				 list1.get(i).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list1.get(i).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list1.get(i).put("discount", list2.get(0).get("discount"));
			 }
		 }
		return list1;
	}
	
	//获取商品
	@Override
	public List<Map<String, String>> ordershowproductL(String oid) {
		String sql = "select pname,num,outprice,pic from order_detail,product where order_detail.pid = product.pid and oid = ?";
		return db.find(sql, oid);
	}
	
	//发货
	@Override
	public int shipments(String oid) {
		String sql1 = "select pid, num from order_detail where oid =" + oid;
		String sql2 = "select inventory from product where pid = ?";
		String sql3 = "update product set inventory = inventory - ? where pid = ?";
		int a = 0;
		List<Map<String, String>> list = db.find(sql1);
		//判断库存
		for(int i = 0; i < list.size(); i++) {
			List<Map<String, String>> l1 = db.find(sql2, list.get(i).get("pid"));
			if(Integer.parseInt(l1.get(i).get("inventory")) < Integer.parseInt(list.get(i).get("num"))) {
				a++;
			}
		}
		if(a > 0) {
			return -1;
		}else {
			//进行减库存
			for(int i = 0; i < list.size(); i++) {
				db.doUpdate(sql3, list.get(i).get("num"), list.get(i).get("pid"));
			}
			String sql = "update `order` set status = 2, time1 = now() where oid = " + oid;
			return db.doUpdate(sql);
		}
	}
	
	//取消订单
	@Override
	public int removebyoidL(String oid) {
		String sql = "update `order` set status = 1 where oid = ?";
		String sql1 = "update order_detail set status = 1 where oid = ?";
		int a = db.doUpdate(sql, oid);
		int b = db.doUpdate(sql1, oid);
		int result = 0;
		if(a > 0) {
			result++;
		}
		if(b > 0) {
			result++;
		}
		return result;
	}
//根据状态查订单
	@Override  
	public List<Map<String, String>> findOrder(String uid, String status) {
		String sql = "select * from `order` where `order`.uid = ? and `order`.`status` = ? order by `order`.oid desc";
		return db.find(sql, uid, status);
	}
	//根据状态查订单详情
	@Override
	public List<Map<String, String>> findDetailOrder(String uid, String oid, String status) {
		String sql = " SELECT product.pid, product.outprice,product.pic,product.pname,`order`.oid,order_detail.num,(order_detail.num*order_detail.price) as countmoney,`order`.time,`order`.`status` "
				+ " FROM `order` "
				+" JOIN order_detail ON `order`.oid = order_detail.oid  "
				+" JOIN product ON order_detail.pid = product.pid  "
				+" JOIN `user` ON `order`.uid = `user`.uid  "
				+" WHERE `order`.oid = ?  "
				+" AND `order`.uid = ?  "
				+" AND `order`.`status` = ? ";
		return db.find(sql, oid,uid,status);
	}
	

	//确认收货地址
	@Override
	public int toRecv(String oid) {
		String sql = "update `order` set time2 = now(),`order`.status = 4 where `order`.oid = ?";
		return db.doUpdate(sql, oid);
	}
	//查出订单详情
	public List<Map<String,String>> showOrder(String oid){
		String sql = "select `order`.did, product.pic,addr.aname,addr.atel,addr.province,addr.city,area,address,pname,order_detail.num,order_detail.price,`order`.countmoneys,`order`.oid,time,`order`.`status` "
				+ " from `order`,order_detail,addr,product where product.pid = order_detail.pid and `order`.aid = addr.aid and order_detail.oid = `order`.oid and `order`.uid = addr.uid and `order`.oid = ?";
		List<Map<String,String>> list1 = db.find(sql, oid);
		for(int i = 0; i < list1.size(); i++) {
			 if("".equals(list1.get(i).get("did")) || list1.get(i).get("did") == null) {
				 list1.get(i).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list1.get(i).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list1.get(i).put("discount", list2.get(0).get("discount"));
			 }
		 }
		System.out.println(list1);
		return list1;
		
	}
	//用户执行可退货操作，将状态改6
	@Override
	public int toTuiHuo(String oid, String uid, String reason) {
		String sql1 = "update `order` set `order`.status = 6 where oid = ?";
		int result1 = db.doUpdate(sql1, oid);
		String sql2 = "insert into reason values(null,?,?,?,1)";
		int result2 = db.doUpdate(sql2, uid,oid,reason);
		if(result1 > 0 && result2 > 0) {
			return 1;
		}else {
			return -1;
		}
	}

	//已完成订单
	@Override
	public List<Map<String,String>> overorderL() {
		String sql = "select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid and a.status in (3,4,5)";
		List<Map<String, String>> list = db.find(sql);
		for(int i = 0; i < list.size(); i++) {
			 if("".equals(list.get(i).get("did")) || list.get(i).get("did") == null) {
				 list.get(i).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list.get(i).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list.get(i).put("discount", list2.get(0).get("discount"));
			 }
		 }
		return list;
	}
	
	//统计今日订单量
	@Override
	public int sumorder() {
		LocalDate today = LocalDate.now();    
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");  
        String formattedDate = today.format(formatter);
		String sql = "select * from `order` where time like '"+formattedDate+"%'";
		List<Map<String, String>> list = db.find(sql);
		return list.size();
	}
	@Override
	public int updatebalance(String oid, String uid, String countmoneys) {
		String sql = "update user set balance = balance - ? where uid = ?";
		db.doUpdate(sql, countmoneys,uid);
		String sql2 = "update user set balance = balance + ? where status = 1";
		db.doUpdate(sql2, countmoneys);
		return 1;
	}
	
	//查询所有订单状态数量
	@Override
	public List<Map<String, String>> getordernumL() {
		String sql = "SELECT  \r\n"
				+ "    SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS a,  \r\n"
				+ "    SUM(CASE WHEN status = 5 THEN 1 ELSE 0 END) AS b,  \r\n"
				+ "    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS c,  \r\n"
				+ "    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS d,  \r\n"
				+ "    SUM(CASE WHEN status = 6 THEN 1 ELSE 0 END) AS e  \r\n"
				+ "FROM  \r\n"
				+ "    `order`  \r\n"
				+ "WHERE  \r\n"
				+ "    status IN (1, 2, 3, 5, 6);";
		return db.find(sql);
	}
	
	//获取本周营业额
	@Override
	public List<Map<String, String>> getweekordernum() {
		String sql = "(SELECT COUNT(*) AS a, CURDATE() AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE())  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 1 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 1 DAY)  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 2 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 2 DAY)  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 3 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 3 DAY)  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 4 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 4 DAY)  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 5 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 5 DAY)  \r\n"
				+ "UNION ALL  \r\n"
				+ "(SELECT COUNT(*) AS a, CURDATE() - INTERVAL 6 DAY AS date  \r\n"
				+ " FROM `order`  \r\n"
				+ " WHERE status NOT IN (0, 7)  \r\n"
				+ "   AND DATE(time) = CURDATE() - INTERVAL 6 DAY);";
		return db.find(sql);
	}
	
	//订单弹框信息
	@Override
	public List<Map<String, String>> openL(String oid) {
		String sql = "select time1,time2,b.aid,b.uid,uname,aname,atel,province,city,area,address,b.oid,countmoneys,did,num,tel,email,b.status,b.time,rea from reason,(select time1,time2,a.aid,uid,uname,aname,atel,province,city,area,address,oid,countmoneys,did,num,tel,email,a.status,a.time from addr,(select time1,time2,`order`.oid,uname,countmoneys,aid,did,time,`order`.num,`order`.`status`,tel,email from `order`, user where `order`.uid = `user`.uid) as a where addr.aid = a.aid) as b where reason.oid = b.oid and b.oid = " + oid;
		List<Map<String, String>> list = db.find(sql);
			 if("".equals(list.get(0).get("did")) || list.get(0).get("did") == null) {
				 list.get(0).put("discount", "");
			 }else {
				 String sql1 = "select discount from discountcard where did = " + list.get(0).get("did");
				 List<Map<String, String>> list2 = db.find(sql1);
				 System.out.println(list2);
				 list.get(0).put("discount", list2.get(0).get("discount"));
			 }
		return list;
	}
	
	//退款
	@Override
	public int refund(String oid) {
		//退款流程
		//1.根据订单详情表对商品进行增加
		//2.修改订单状态
		//3.修改用户余额，减去店长余额
		
		//根据oid查询出全部订单商品
		String sql1 = "select * from order_detail where oid = " + oid;
		String sql2 = "update product set inventory = inventory + ? where pid = ?";
		String sql3 = "update `order` set status = 7 where oid = ?";
		String sql4 = "select uid from `order` where oid = ?";
		String sql5 = "update user set balance = balance + ? where uid = ?";
		String sql6 = "select countmoneys from `order` where oid = ?";
		String sql7 = "update user set balance = balance - ? where status = 1";
		List<Map<String, String>> list1 = db.find(sql1);
		for(int i = 0; i < list1.size(); i++) {
			db.doUpdate(sql2, list1.get(i).get("num"), list1.get(i).get("pid"));
		}
		//修改订单状态
		db.doUpdate(sql3, oid);
		//修改余额
		//根据oid查询uid
		List<Map<String, String>> list4 = db.find(sql4, oid);
		//增加用户余额
		//查询订单表实付款
		List<Map<String, String>> list6 = db.find(sql6, oid);
		db.doUpdate(sql5, list6.get(0).get("countmoneys"), list4.get(0).get("uid"));
		int result = db.doUpdate(sql7, list6.get(0).get("countmoneys"));
		return result;
	}
	
	//获取数据
	@Override
	public String getdataL() {
		//用户总人数
		//订单除了已退款的总金额
		//订单总数
		//退款订单数/订单总数
		String sql = "select count(*)-1 a from user";
		List<Map<String, String>> list = db.find(sql);
		String usernum = list.get(0).get("a");	//总人数
		String sql1 = "select sum(countmoneys) b from `order` where `status` not in (7)";
		List<Map<String, String>> list1 = db.find(sql1);
		String moneynum = list1.get(0).get("b");	//总钱
		String sql2 = "select count(*) c from `order`";
		List<Map<String, String>> list2 = db.find(sql2);
		String ordernum = list2.get(0).get("c");	//总订单
		String sql3 = "select count(*) d from `order` where status = 7";
		List<Map<String, String>> list3 = db.find(sql3);
		String refundnum = list3.get(0).get("d");	//退款订单
		double e = (Double.parseDouble(refundnum)/Double.parseDouble(ordernum));
		String f = String.format("%.2f", e);
		double decimalValue = Double.parseDouble(f);
		double multipliedValue = decimalValue * 100;
		int integerValue = (int) multipliedValue;
		String h = Integer.toString(integerValue);
		String data = usernum + ":" + moneynum + ":" + ordernum + ":" + h;
		return data;
	}
	
	//查询12个月的订单
	@Override
	public List<Map<String, String>> getmonth(String year) {
		String sql = "SELECT   \r\n"
				+ "    MONTH(time) AS a,  \r\n"
				+ "    SUM(countmoneys) AS b\r\n"
				+ "FROM   \r\n"
				+ "    `order`  \r\n"
				+ "WHERE   \r\n"
				+ "    YEAR(time) = ?\r\n"
				+ "    AND status <> 7  \r\n"
				+ "GROUP BY   \r\n"
				+ "    MONTH(time)  \r\n"
				+ "ORDER BY   \r\n"
				+ "    a;";
		return db.find(sql, year);
	}
	
	//获取5年的销售额
	@Override
	public List<Map<String, String>> getyear() {
		String sql = "SELECT  \r\n"
				+ "    YEAR(time) AS a,  \r\n"
				+ "    COUNT(*) AS b  \r\n"
				+ "FROM  \r\n"
				+ "    `order`\r\n"
				+ "WHERE  \r\n"
				+ "    status != 7  \r\n"
				+ "    AND YEAR(time) BETWEEN YEAR(CURDATE()) - 4 AND YEAR(CURDATE())  \r\n"
				+ "GROUP BY  \r\n"
				+ "    YEAR(time)  \r\n"
				+ "ORDER BY  \r\n"
				+ "    a;";
		return db.find(sql);
	}
	
	//获取季度
	@Override
	public List<Map<String, String>> getjd(String year) {
		String sql = "SELECT  \r\n"
				+ "    QUARTER(time) AS a,  \r\n"
				+ "    SUM(countmoneys) AS b  \r\n"
				+ "FROM  \r\n"
				+ "    `order`  \r\n"
				+ "WHERE  \r\n"
				+ "    YEAR(time) = ?\r\n"
				+ "    AND status != 7  \r\n"
				+ "GROUP BY  \r\n"
				+ "    QUARTER(time)  \r\n"
				+ "ORDER BY  \r\n"
				+ "    a;";
		return db.find(sql, year);
	}
}
